SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'fnCreateUniqueUsername' AND ROUTINE_TYPE = 'FUNCTION')
	DROP FUNCTION fnCreateUniqueUsername
GO

CREATE FUNCTION fnCreateUniqueUsername
(
	-- Add the parameters for the function here
	@friendlyName varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
	-- Declare the return variable here	
	DECLARE @UserName varchar(255)
	
	DECLARE @allowedCharacters varchar(255)
	DECLARE @index int
	DECLARE @len int
	DECLARE @char varchar(1)
	
	SET @allowedCharacters = 'ABCDEFGEHIJKLMNOPQRSTUVWXYZ'
	SET @index = 1
	SET @len = LEN(@friendlyName)
	SET @UserName = ''
	
	WHILE @index<= @len
	BEGIN	
		set @char = UPPER(SUBSTRING(@friendlyName, @index, 1))
		--print CHARINDEX(@char, @allowedCharacters)
		IF CHARINDEX(@char, @allowedCharacters) > 0
		BEGIN
		--print @UserName + @char
		SET @UserName = @UserName + @char
		END
	SET @index = @index + 1
	END

	RETURN @UserName
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sptblPersons_CreateLogin' AND ROUTINE_TYPE = 'PROCEDURE')
	DROP PROCEDURE sptblPersons_CreateLogin
GO

CREATE PROCEDURE sptblPersons_CreateLogin
	-- Add the parameters for the stored procedure here
	@prs_ID int	
AS
BEGIN 
	DECLARE @TranCount int
	SET @TranCount = @@TRANCOUNT 
	IF @TranCount > 0
		SAVE TRAN sptblPersons_CreateLogin
	ELSE
		BEGIN TRAN
	
	DECLARE @FullName varchar(45)
	DECLARE @UserName varchar(50)
	DECLARE @Suffix int
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    BEGIN TRY
    
    IF EXISTS (
		SELECT 1 FROM dbo.tblPersons WHERE prs_ID = @prs_ID AND prs_Username IS NULL
    )
    BEGIN		
		SET @Suffix = 1
		SELECT TOP 1 @FullName = prs_Lastname + prs_Initials FROM dbo.tblPersons WHERE prs_ID = @prs_ID
		SET @FullName = dbo.fnCreateUniqueUsername(@FullName)
		SET @UserName = @FullName + Right(convert(char(4), (1000 + @Suffix)), 3)
		
		WHILE EXISTS (SELECT 1 FROM dbo.tblUFGUsers WHERE usr_UserName = @UserName)
		BEGIN
			SET @Suffix = @Suffix+1
			SET @UserName = @FullName + Right(convert(char(4), (1000 + @Suffix)), 3)			
		END		
		
		INSERT tblUFGUsers (usr_UserName,usr_Password,usr_PasswordSalt)
		VALUES (@UserName,HASHBYTES('sha1',@UserName),'')
		
		INSERT tblUFGUsersInRoles (uir_UserName, uir_RoleName) VALUES (@UserName, 'Leden')
		
		UPDATE tblPersons SET prs_Username = @UserName WHERE prs_ID = @prs_ID
    END
    
    IF @TranCount = 0
		COMMIT TRAN
    
	END TRY
	BEGIN CATCH
	    DECLARE @ErrorMessage NVARCHAR(4000);
	    DECLARE @ErrorSeverity INT;
	    DECLARE @ErrorState INT;
	    SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();
	    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	    
	    IF @TranCount > 0
			ROLLBACK TRAN sptblPersons_CreateLogin
		ELSE
			ROLLBACK TRAN
	    
	END CATCH
	END
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sptblPersons_CreateLoginAction' AND ROUTINE_TYPE = 'PROCEDURE')
	DROP PROCEDURE sptblPersons_CreateLoginAction
GO

CREATE PROCEDURE sptblPersons_CreateLoginAction
	-- Add the parameters for the stored procedure here
	@prs_ID int	
AS
BEGIN
	SET NOCOUNT ON	

    -- Insert statements for procedure here
    BEGIN TRY
      
		IF EXISTS ( SELECT 1 FROM dbo.tblPersons WHERE prs_ID = @prs_ID AND prs_Username IS NULL )
		BEGIN
			SELECT CAST(1 AS BIT)
		END
		ELSE
		BEGIN
			SELECT CAST(0 AS BIT)
		END    
	END TRY
	BEGIN CATCH
	    DECLARE @ErrorMessage NVARCHAR(4000)
	    DECLARE @ErrorSeverity INT
	    DECLARE @ErrorState INT
	    SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();
	    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)	    
	END CATCH	
END
GO

DELETE FROM [tblUFGMenuActions] WHERE [uma_TableName] = 'tblPersons' AND [uma_SequenceNumber] = 1
GO

INSERT INTO [tblUFGMenuActions]
           ([uma_TableName]
           ,[uma_SequenceNumber]
           ,[uma_StoredProcedure]
           ,[uma_Icon]
           ,[uma_ShowTable]
           ,[uma_ShowConditionProcedure]
           ,[uma_ToggleVisible]
           ,[uma_VisibleInGrid]
           ,[uma_PanelTable]
           ,[uma_PanelNumber]
           ,[uma_HAlign]
           ,[uma_VAlign]
           ,[uma_ValidatesData] )          
     VALUES
           ('tblPersons'
           ,1
           ,'sptblPersons_CreateLogin'
           ,'icon_user.gif'
           ,null
           ,'sptblPersons_CreateLoginAction'
           ,1
           ,0
           ,'tblPersons'
           ,1
           ,'Right'
           ,'Top'
           ,1)
GO

INSERT INTO dbo.tblUFGMenuActionRoles
		(uar_TableName,
		 uar_SequenceNumber,
		 uar_RoleName)
	VALUES
		('tblPersons',
		 1,
		 'Ledenadministratie Bond'),
		 ('tblPersons',
		 1,
		 'Ledenadministratie Club')
GO

INSERT INTO dbo.tblUFGMenuActionTitles
		(uat_TableName,
		 uat_SequenceNumber,
		 uat_Language,
		 uat_Title,
		 uat_Tooltip,
		 uat_ShowConditionDescription,
		 uat_ConfirmationMessage)
	VALUES
		('tblPersons', 1, 'Nederlands', 'Login maken', 'Nieuwe gebruikersnaam en wachtwoord toekennen', null, 'Een nieuwe login voor deze gebruiker maken?')
GO
		 